iT邦幫忙

DAY 18
5

OpenAccess ORM for Oracle 實戰演練系列 第 14

OpenAccess ORM for Oracle 實戰演練 Day 18

  • 分享至 

  • xImage
  •  

取得OpenAccess ORM轉換、執行的Oracle Sql Statements
透過 ORM 框架存取資料庫,雖然目的就是我們可以省去多撰寫一種語言(Sql Script)的麻煩,加快開發速度,但是畢竟資料庫可以接受的語法還是 Sql,所以我們還是要了解 OpenAccess ORM 幫我們轉換、真實執行的 Sql 是什麼。

PS. 小孩報到的第一天,真是無比忙碌,差點鐵人賽就要中斷了,好不容易寫完要貼文,又遇上 ithelp 死站,嚇的我一身冷汗……

要查閱 OpenAccess ORM 所轉換出來的 Sql 語法,要透過 StringWriter 物件處理,我們切換到 Model 專案,在 ProductMgmt 類別中加一些處理:

Public Class ProductsMgmt
    Private cxt As SecondModel
    Private output As IO.StringWriter
    Public Sub New()
        cxt = New SecondModel()
        output = New IO.StringWriter()
        cxt.Log = output
    End Sub

    Public Function TraceSQLStatements() As String
        If output IsNot Nothing Then
            Return output.ToString()
        Else
            Return Nothing
        End If
    End Function

    Public Function GetGiantProducts() As IList(Of Products)
        Dim query = From x In cxt.Products
                    Where x.PRODUCTNAME.Contains("巨人")
                    Select x
        Return query.ToList()
    End Function

    Public Function DeleteProduct(Id As Long) As Boolean
        Dim product = GetProduct(Id)
        If product Is Nothing Then
            Throw New System.NullReferenceException(String.Format("查無產品編號:{0} 的資料。", Id))
        End If
        cxt.Delete(product)
        cxt.SaveChanges()
        Return True
    End Function

    Public Function GetProduct(ByVal Id As Long) As Products
        Dim product = (From x In cxt.Products
                              Where x.PRODUCTID = Id
                              Select x).FirstOrDefault()
        Return product
    End Function
End Class

和先前版本的差異,主要在於建構子裡面,我們在建立物件容器執行個體後,再建立一個 StringWirter 執行個體,並指派給物件容器的 Log 屬性,然後再加入一個新函式 TraceSQLStatements(),會調用剛剛建立的 StringWriter 物件之 ToString() 函式。

照例,我們建立一個單元測試來驗證 TraceSQLStatements() 是否能正確執行:

<TestMethod()> _
Public Sub TraceSQLStatementsTest()
    Dim target As ProductsMgmt = New ProductsMgmt()
    Dim actual As IList(Of Products)
    actual = target.GetGiantProducts
    Assert.IsTrue(actual.Any)
    Assert.IsNotNull(target.TraceSQLStatements())
End Sub

當然,測試一定要通過,接著我們到 Web 專案中,新增 ShowTraceSQLStatements.aspx 頁面,加入以下程式碼:

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="ShowTraceSQLStatements.aspx.vb" Inherits="OpenAccessWebApp01.ShowTraceSQLStatements" %>


<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title></title>


    <form id="form1" runat="server">
        <div>
            <asp:Button ID="btnGetGiant" runat="server" Text="取得資料並顯示Sql" />
            <br />
            輸入要刪除的 ProductId:<asp:TextBox ID="txtId" runat="server"></asp:TextBox>
            <asp:Button ID="btnDelete" runat="server" Text="刪除資料" />
            <hr />
            實際執行的SQL Statements:<br />
            <asp:Label ID="lblSQL" runat="server" Text=""></asp:Label>
            <hr />
            <asp:GridView ID="gvList" runat="server"></asp:GridView>
        </div>
    </form>

ShowTraceSQLStatements.aspx.vb

Imports OpenAccessWebApp01Model

Public Class ShowTraceSQLStatements
    Inherits System.Web.UI.Page

    Protected Sub btnGetGiant_Click(sender As Object, e As EventArgs) Handles btnGetGiant.Click
        DataBinding(New ProductsMgmt())
    End Sub
    Private Sub DataBinding(ByVal bo As ProductsMgmt)
        gvList.DataSource = bo.GetGiantProducts()
        gvList.DataBind()
        lblSQL.Text = bo.TraceSQLStatements()
    End Sub

    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        Dim bo = New ProductsMgmt()
        If  IsNumeric(txtId.Text) Then
            bo.DeleteProduct(txtId.Text)
        End If
        DataBinding(bo)
    End Sub
End Class

執行結果如下:

上面 Gif 圖檔的連結:http://i.minus.com/inZs3VWqqGaNt.Gif


上一篇
OpenAccess ORM for Oracle 實戰演練 Day 17
下一篇
OpenAccess ORM for Oracle 實戰演練 Day 19
系列文
OpenAccess ORM for Oracle 實戰演練26
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 則留言

0
SunAllen
iT邦研究生 1 級 ‧ 2013-10-10 23:43:52

smartleos 大大...沙發

請問您的gif軟體是那一套,能介紹一下嗎?臉紅

我只看的懂gif的圖...還有中文...哭

smartleos iT邦新手 3 級 ‧ 2013-10-11 00:01:48 檢舉

我用 Cropper + pluggin 來抓圖直接轉 GIF。唱歌
http://cropper.codeplex.com/
http://cropperplugins.codeplex.com/

我要留言

立即登入留言